/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.agency8x.web.util;

import com.jfinal.plugin.activerecord.Record;
import com.jfinal.template.expr.ast.Array;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

/**
 * POI Excel导出类
 * @author SoChishun
 * @version 1.0 2017-5-26
 * @see http://blog.csdn.net/evangel_z/article/details/7332535
 * @see http://www.oschina.net/question/2484843_2140180
 */
public class PoiExcelExporter {
    
    private List<Record> data;
    private String[] headers;
    private String[] fields;
    private String fileName;
    private String version;
    private String sheetName;
    private int headerRowIndex;
    private String dateFormat;
    
    /**
     * 构造方法
     * @param data
     * @param fileName
     * @param sheetName 
     */
    public PoiExcelExporter (List<Record> data,String fileName,String sheetName) {
        this.data = data;
        this.fileName=fileName;
        this.sheetName=sheetName;
        this.version="1.0.0.0";
        this.headerRowIndex=0;
        this.dateFormat = "yyyy-MM-dd";
    }
    
    /**
     * 生成数据
     * @param outStream 
     * @example response.getOutputStream()
     */
    @SuppressWarnings("unchecked")
    public void export(OutputStream outStream) {
    	export(null,outStream);
    }
    @SuppressWarnings("unchecked")
    public void export(HashMap<String,String> headerMap,OutputStream outStream) {

        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 创建字体样式
        /*
        HSSFFont font = wb.createFont();
        font.setFontName("Verdana");
        font.setBold(true);
        font.setFontHeight((short) 300);
        font.setColor(IndexedColors.BLUE.index);
        // 创建表头单元格样式
        HSSFCellStyle styleHead = wb.createCellStyle();
        styleHead.setAlignment(HorizontalAlignment.CENTER);
        styleHead.setVerticalAlignment(VerticalAlignment.CENTER);
        styleHead.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.index);
        styleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置边框
        styleHead.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.index);
        styleHead.setBorderBottom(BorderStyle.THIN);
        styleHead.setBorderLeft(BorderStyle.THIN);
        styleHead.setBorderRight(BorderStyle.THIN);
        styleHead.setBorderTop(BorderStyle.THIN);
        //styleHead.setFont(font);// 设置字体
        */
        
        // 创建表头单元格样式
        HSSFFont font = wb.createFont();
        font.setBold(true);
        HSSFCellStyle styleHead = wb.createCellStyle();
        styleHead.setFont(font);// 设置字体
        
        // 设置内容单元格格式
        HSSFCellStyle styleBody = wb.createCellStyle();
        styleBody.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
        styleBody.setWrapText(true);// 自动换行
        
        // 填充内容
        HSSFCell cell; // 声明一个单元格
        HSSFRow row; // 声明一个表格行
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 声明一个画图的顶级管理器

        if(null!=headerMap){
        	Set<String> hmKeys=headerMap.keySet();
        	Collection<String> hmValues=	headerMap.values();
        	headers=hmKeys.toArray(new String[ hmKeys.size()]);
        	fields=headerMap.keySet().toArray(new String[hmKeys.size()]);
        }
        if (null == fields) {
            fields = data.get(0).getColumnNames();
        }
        if (null == headers) {
            headers = fields;
        }
        // 生成表格标题行   
        int rowIdx = headerRowIndex;
        row = sheet.createRow(rowIdx);
        for (short i = 0; i < headers.length; i++) {
            // 创建单元格
            cell = row.createCell(i);
            // 给Excel的单元格设置样式和赋值
            cell.setCellStyle(styleHead);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            // 设置excel每列宽度
            sheet.setColumnWidth(i, 3500);
        }

        // 生成内容行
        Object value;
        String textValue;
        // 遍历集合数据，产生数据行 
        for (Record record:data) {
            rowIdx++;
            row = sheet.createRow(rowIdx);
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
            for (short i = 0; i < fields.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(styleBody);
                try {
                    value = record.get(fields[i]);
                    // 判断值的类型后进行强制类型转换  
                    textValue = null;
                    if (null != value) {
                        if (value instanceof Boolean) {
                            boolean bValue = (Boolean) value;
                            textValue = "男";
                            if (!bValue) {
                                textValue = "女";
                            }
                        } else if (value instanceof Date) {
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
                            textValue = sdf.format(date);
                        } else if (value instanceof byte[]) {
                            // 有图片时，设置行高为60px;  
                            row.setHeightInPoints(60);
                            // 设置图片所在列宽度为80px,注意这里单位的一个换算  
                            sheet.setColumnWidth(i, (short) (35.7 * 80));
                            // sheet.autoSizeColumn(i);  
                            byte[] bsValue = (byte[]) value;
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                                    1023, 255, (short) 6, rowIdx, (short) 6, rowIdx);
                            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
                            patriarch.createPicture(anchor, wb.addPicture(
                                    bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                        } else {
                            // 其它数据类型都当作字符串简单处理  
                            textValue = value.toString();
                        }
                    }
                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成  
                    if (textValue != null) {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理  
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(
                                    textValue);
                            //HSSFFont font3 = wb.createFont();
                            //font3.setColor(IndexedColors.BLUE.index);
                            //richString.applyFont(font3);
                            cell.setCellValue(richString);
                        }
                    } else {
                        textValue = "";
                    }
                } catch (SecurityException | IllegalArgumentException e) {
                    e.printStackTrace();
                } finally {
                    // 清理资源  
                }
            }
        }
        try {
            wb.write(outStream);
        } catch (IOException ex) {
            Logger.getLogger(PoiExcelExporter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @SuppressWarnings("unchecked")
    public void exportExcel(String sheetName, String[] headers, List<Record> dataset, OutputStream outStream, String datePattern) {
        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 设置excel每列宽度
        //sheet.setColumnWidth(0, 4000);
        //sheet.setColumnWidth(1, 3500);
        // 创建字体样式
        //HSSFFont font = wb.createFont();
        //font.setFontName("Verdana");
        //font.setBold(true);
        //font.setFontHeight((short) 300);
        //font.setColor(IndexedColors.BLUE.index);
        // 创建表头单元格样式
        HSSFCellStyle styleHead = wb.createCellStyle();
        styleHead.setAlignment(HorizontalAlignment.CENTER);
        styleHead.setVerticalAlignment(VerticalAlignment.CENTER);
        styleHead.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.index);
        styleHead.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置内容单元格格式
        HSSFCellStyle styleBody = wb.createCellStyle();
        styleBody.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
        styleBody.setWrapText(true);// 自动换行

        // 设置边框
        styleHead.setBottomBorderColor(IndexedColors.RED.index);
        styleHead.setBorderBottom(BorderStyle.THIN);
        styleHead.setBorderLeft(BorderStyle.THIN);
        styleHead.setBorderRight(BorderStyle.THIN);
        styleHead.setBorderTop(BorderStyle.THIN);

        //styleHead.setFont(font);// 设置字体
        // 填充内容
        fillContentForModel(wb, sheet, styleHead, styleBody, headers, dataset, datePattern);
        try {
            wb.write(outStream);
        } catch (IOException ex) {
            Logger.getLogger(PoiExcelExporter.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void fillContentForModel(HSSFWorkbook wb, HSSFSheet sheet, HSSFCellStyle styleHead, HSSFCellStyle styleBody, String[] headers, List<Record> dataset, String pattern) {

        HSSFCell cell; // 声明一个单元格
        HSSFRow row; // 声明一个表格行
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 声明一个画图的顶级管理器

        String[] fields = dataset.get(0).getColumnNames();
        if (null == headers) {
            headers = fields;
        }
        // 生成表格标题行   
        int rowIdx = 0;
        row = sheet.createRow(rowIdx);
        for (short i = 0; i < headers.length; i++) {
            // 创建单元格
            cell = row.createCell(i);
            // 给Excel的单元格设置样式和赋值
            cell.setCellStyle(styleHead);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        // 生成内容行
        Object value;
        String textValue;
        // 遍历集合数据，产生数据行 
        for (Record record : dataset) {
            rowIdx++;
            row = sheet.createRow(rowIdx);
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
            for (short i = 0; i < fields.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(styleBody);
                try {
                    value = record.get(fields[i]);
                    // 判断值的类型后进行强制类型转换  
                    textValue = null;
                    if (null != value) {
                        if (value instanceof Boolean) {
                            boolean bValue = (Boolean) value;
                            textValue = "男";
                            if (!bValue) {
                                textValue = "女";
                            }
                        } else if (value instanceof Date) {
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            textValue = sdf.format(date);
                        } else if (value instanceof byte[]) {
                            // 有图片时，设置行高为60px;  
                            row.setHeightInPoints(60);
                            // 设置图片所在列宽度为80px,注意这里单位的一个换算  
                            sheet.setColumnWidth(i, (short) (35.7 * 80));
                            // sheet.autoSizeColumn(i);  
                            byte[] bsValue = (byte[]) value;
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                                    1023, 255, (short) 6, rowIdx, (short) 6, rowIdx);
                            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
                            patriarch.createPicture(anchor, wb.addPicture(
                                    bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                        } else {
                            // 其它数据类型都当作字符串简单处理  
                            textValue = value.toString();
                        }
                    }
                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成  
                    if (textValue != null) {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理  
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(
                                    textValue);
                            HSSFFont font3 = wb.createFont();
                            font3.setColor(IndexedColors.BLUE.index);
                            richString.applyFont(font3);
                            cell.setCellValue(richString);
                        }
                    } else {
                        textValue = "";
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } finally {
                    // 清理资源  
                }
            }
        }
    }
}
